set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_dm.dm_route_network_control_dt
select
     a.code
    ,a.name
    ,'新增网点' as type
    ,'{{ execution_date | cst_ds }}' as dt
from (
    select
    code,name
    from jms_ods.yl_lmdm_sys_network
    where dt = '{{ execution_date | cst_ds }}'
    and type_id = 336
    and is_entrepot = 2
    and is_enable = 1
) a --今日网点
left join (
    select
    code,name
    from jms_ods.yl_lmdm_sys_network
    where dt = date_sub('{{ execution_date | cst_ds }}',1)
    and type_id = 336
    and is_entrepot = 2
    and is_enable = 1
) b on a.code = b.code
where b.code is null
union all
select
     a.code
    ,a.name
    ,'删除网点' as type
    ,'{{ execution_date | cst_ds }}' as dt
from (
    select
    code,name
    from jms_ods.yl_lmdm_sys_network
    where dt = date_sub('{{ execution_date | cst_ds }}',1)
    and type_id = 336
    and is_entrepot = 2
    and is_enable = 1
) a --今日网点
left join (
    select
    code,name
    from jms_ods.yl_lmdm_sys_network
    where dt = '{{ execution_date | cst_ds }}'
    and type_id = 336
    and is_entrepot = 2
    and is_enable = 1
) b on a.code = b.code
where b.code is null
distribute by dt,pmod(hash(rand()),1);



insert overwrite table jms_dm.dm_route_cainiao_detail_control_dt
select
     route.in_from_code as network_code
    ,route.in_from_name as network_name
    ,route.total_days as total_days --T+时效
    ,case when dim.type is not null then dim.type
          when route.is_enable = 1  then '时效新增'
          when route.is_enable <> 1 then '时效删除'
          end as type --类型
    ,sum(route.cnt) as cnt --散货网点数
    ,sum(route.line_cnt  ) as line_cnt --线路数
    ,sum(route.route_cnt )  as route_cnt --路由数
    ,'{{ execution_date | cst_ds }}' as dt
from (
    select
         in_from_code
        ,in_from_name
        ,route.is_enable
        ,route.out_sign_days as total_days
        ,count(distinct end_center) as line_cnt
        ,count(distinct route.out_to_code) as cnt --散货网点数
        ,count(1) as route_cnt
    from (
        select
             in_from_code
            ,in_from_name
            ,out_to_code
            ,out_to_name
            ,end_center --目的网点
            ,out_sign_days
            ,is_enable
        from (
            select
                 in_from_code  as in_from_code
                ,in_from_name  as in_from_name
                ,out_to_code   as out_to_code
                ,out_to_name   as out_to_name
                ,coalesce(e7_code,e6_code,e5_code,e4_code,e3_code,e2_code,e1_code) as end_center --目的网点
                ,out_sign_days
                ,is_enable
            from jms_dm.dm_cainiao_route_push_data_version2_dt
            where dt = '{{ execution_date | cst_ds }}'
        ) a group by in_from_code
                    ,in_from_name
                    ,out_to_code
                    ,out_to_name
                    ,end_center --目的网点
                    ,out_sign_days
                    ,is_enable
    ) route
    group by route.in_from_code
            ,route.in_from_name
            ,route.out_sign_days
            ,route.is_enable
) route
left join (
    select *
    from jms_dm.dm_route_network_control_dt dim
    where dt = '{{ execution_date | cst_ds }}'
) dim on route.in_from_code  = dim.code
group by in_from_code
        ,in_from_name
        ,total_days
        ,case when dim.type is not null then dim.type
          when route.is_enable = 1  then '时效新增'
          when route.is_enable <> 1 then '时效删除'
          end
distribute by dt,pmod(hash(rand()),5);

insert overwrite table jms_dm.dm_route_cainiao_control_dt
select
     '{{ execution_date | cst_ds }}' as stat_date
    ,a.add_route_cnt
    ,a.del_route_cnt
    ,b.add_network_cnt
    ,a.add_network_route_cnt
    ,b.del_network_cnt
    ,a.del_network_route_cnt
    ,a.add_effe_route_cnt
    ,a.add_effe_line_cnt
    ,a.add_effe_cnt
    ,a.del_effe_route_cnt
    ,a.del_effe_line_cnt
    ,a.del_effe_cnt
    ,a.dt
from (
    select
         '{{ execution_date | cst_ds }}' as stat_date
        ,nvl(sum(case when type in ('新增网点','时效新增') then route_cnt end),0) as add_route_cnt
        ,nvl(sum(case when type in ('时效删除','删除网点') then route_cnt end),0) as del_route_cnt
        ,nvl(sum(case when type = '新增网点' then cnt       end),0) as add_network_cnt
        ,nvl(sum(case when type = '新增网点' then route_cnt end),0) as add_network_route_cnt
        ,nvl(sum(case when type = '删除网点' then cnt       end),0) as del_network_cnt
        ,nvl(sum(case when type = '删除网点' then route_cnt end),0) as del_network_route_cnt
        ,nvl(sum(case when type = '时效新增' then route_cnt end),0) as add_effe_route_cnt
        ,nvl(sum(case when type = '时效新增' then line_cnt  end),0) as add_effe_line_cnt
        ,nvl(sum(case when type = '时效新增' then cnt       end),0) as add_effe_cnt
        ,nvl(sum(case when type = '时效删除' then route_cnt end),0) as del_effe_route_cnt
        ,nvl(sum(case when type = '时效删除' then line_cnt  end),0) as del_effe_line_cnt
        ,nvl(sum(case when type = '时效删除' then cnt       end),0) as del_effe_cnt
        ,dt
    from jms_dm.dm_route_cainiao_detail_control_dt
    where dt = '{{ execution_date | cst_ds }}'
    group by dt
) a 
left join (
    select 
         count(case when type = '删除网点' then code end) as del_network_cnt
        ,count(case when type = '新增网点' then code end) as add_network_cnt
		,dt
    from jms_dm.dm_route_network_control_dt
    where dt = '{{ execution_date | cst_ds }}'
	group by dt
) b on a.dt = b.dt
distribute by dt,pmod(hash(rand()),1);
